﻿using Microsoft.Win32;
using MiniExcelLibs;
using OfficeOpenXml.FormulaParsing.Excel.Functions.Text;
using Sunny.UI;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace PanhuaWin.车间
{
    public partial class Form镀锌投入产出表 : UIPage
    {
        int 开始行;
        int 结束行;
        string 工厂编号 = "3001";
        string FormWorkType = "镀锌";
        int 开始列;
        int 结束列;
        public Form镀锌投入产出表()
        {
            InitializeComponent();
        }

        private void uiButton查询_Click(object sender, EventArgs e)
        {
            Guid guid = Guid.NewGuid();
            //DataTable tb = ListOutPutData();
            DataTable tb = ListOutPutData(guid.ToString());
            uiDataGridView1.AutoGenerateColumns = false;
            this.uiDataGridView1.DataSource = tb;
            //this.uiDataGridView1.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells;
            this.uiDataGridView1.Refresh();
            for (int i = 0; i < this.uiDataGridView1.Rows.Count; i++)
            {
                if (this.uiDataGridView1.Rows[i].Cells["E产出等级"].Value.ToString() == "B")
                {
                    this.uiDataGridView1.Rows[i].Cells["E产出等级"].Style.BackColor = Color.FromArgb(255, 255, 128);
                }
                else if (this.uiDataGridView1.Rows[i].Cells["E产出等级"].Value.ToString() == "S")
                {
                    this.uiDataGridView1.Rows[i].Cells["E产出等级"].Style.BackColor = Color.FromArgb(255, 0, 0);
                }
            }
            //Guid guid = Guid.NewGuid();
            deleteTable(guid.ToString());
        }

        private DataTable ListOutPutData(string guid)
        {
            //string guid = Guid.NewGuid().ToString();
            decimal 总投入重量 = 0;
            decimal 总产出重量 = 0;
            decimal 产出米数 = 0;
            decimal 总面积 = 0;
            string sql;
            sql = "SELECT '" + guid + "',  row_number() over(order by 投入卷号) 序号, 投入卷号, 投入规格, 投入重量, 投入材质, 生产日期, 产出卷号, ";
            sql += " 产出规格, 产出材质,理算厚度, 理算长度, 成品宽度, 实际宽度, ";
            sql += " 产出重量, 产出等级, 去向, 产线名称, ";
            sql += " 班组名称, 班别,客户,锌层,长度,产地,包装,报工人,报工时间,修改人,修改时间,是否抛送NC,是否已打印,品名,面积,质检信息 FROM      View_镀锌投入产出明细表 ";
            sql += " where 1=1 ";
            //and 生产日期 >='" + this.uiDatePicker开始日期.Value.ToString() + "'";
            //sql += " and 生产日期 < '" + this.uiDatePicker结束日期.Value.AddDays(1) + "'";

            //string sql2 = "select isnull(sum(投入重量),0) ,isnull(sum(产出重量),0),isnull(sum(长度),0)  FROM      View_镀锌投入产出明细表 ";
            string sql2 = "SELECT distinct 投入卷号,投入重量 FROM View_镀锌投入产出明细表 ";
            sql2 += " where 1=1  and 生产日期 >='" + this.uiDatePicker开始日期.Value.ToString() + "'";
            sql2 += " and 生产日期 < '" + this.uiDatePicker结束日期.Value.AddDays(1) + "'";

            if (this.uiComboBox查询产线.SelectedText != "全部")
            {
                sql += " and 产线名称='" + this.uiComboBox查询产线.SelectedText + "' ";
                sql2 += " and 产线名称='" + this.uiComboBox查询产线.SelectedText + "' ";
            }
            if (this.uiComboBox查询等级.SelectedText != "全部")
            {
                sql += " and 产出等级='" + this.uiComboBox查询等级.SelectedText + "' ";
                sql2 += " and 产出等级='" + this.uiComboBox查询等级.SelectedText + "' ";
            }
            if (this.uiComboBox查询班组.SelectedText != "全部" && !string.IsNullOrEmpty(this.uiComboBox查询班组.SelectedText))
            {
                sql += " and 班组名称='" + this.uiComboBox查询班组.SelectedText + "' ";
                sql2 += " and 班组名称='" + this.uiComboBox查询班组.SelectedText + "' ";
            }
            if (this.uiTextBox投入卷号包含.Text != "")
            {
                sql += " and 投入卷号 like '%" + this.uiTextBox投入卷号包含.Text.Trim().Replace("--", "") + "%' ";
                sql2 += " and 投入卷号 like '%" + this.uiTextBox投入卷号包含.Text.Trim().Replace("--", "") + "%' ";
            }
            if (this.uiTextBox产出卷号包含.Text != "")
            {
                sql += " and 产出卷号 like '%" + this.uiTextBox产出卷号包含.Text.Trim().Replace("--", "") + "%' ";
                sql2 += " and 产出卷号 like '%" + this.uiTextBox产出卷号包含.Text.Trim().Replace("--", "") + "%' ";
            }

            sql += " order by 报工时间,投入卷号";

            //塞入镀锌投入产出表
            string sqlInsert = @"insert into [dbo].[Bus_镀锌投入产出] (
                                        [guid], [序号], [投入卷号], [投入规格],  [投入重量], [投入材质], [生产日期], [产出卷号], [产出规格], [产出材质], [理算厚度], 
[理算长度], [成品宽度], [实际宽度], [产出重量], [产出等级], [去向], [产线名称], [班组名称], [班别], [客户], [锌层], [长度], [产地], [包装], [报工人],
[报工时间], [修改人], [修改时间], [是否抛送NC], [是否已打印], [品名], [面积],质检信息)";

            sqlInsert += sql;
            pub.ExecuteSql(pub.ConnectionString, sqlInsert);

            //计算分摊重量
            //镀锌投入产出分摊(guid.ToString(), this.uiDatePicker开始日期.Value, this.uiDatePicker结束日期.Value.AddDays(1));
            sql = "  update a set a.产出汇总重量=b.产出汇总重量   from [Bus_镀锌投入产出] a ,(select 投入卷号 ,sum(产出重量) 产出汇总重量 ";
            sql += " from [Bus_镀锌投入产出] where guid='" + guid + "' group by 投入卷号 ) b  where a.投入卷号=b.投入卷号 and a.guid='" + guid + "' ";
            pub.ExecuteSql(pub.ConnectionString, sql);
            sql = "     update [Bus_镀锌投入产出] set  分摊重量=投入重量*产出重量/产出汇总重量 where guid='" + guid + "'  ";
            pub.ExecuteSql(pub.ConnectionString, sql);


            sql = "select * from Bus_镀锌投入产出 where guid = '" + guid + "' and 生产日期 >= '" + this.uiDatePicker开始日期.Value.ToString("yyyy-MM-dd") + "' and 生产日期 < '" + uiDatePicker结束日期.Value.AddDays(1).ToString("yyyy-MM-dd") + "'";
            DataTable tb = pub.GetDataTable(pub.ConnectionString, sql);
            if (tb != null && tb.Rows.Count > 0)
            {
                for (int i = 0; i < tb.Rows.Count; i++)
                {
                    string 投入卷号 = tb.Rows[i]["投入卷号"].ToString();
                    string 投入重量 = tb.Rows[i]["投入重量"].ToString();
                    //等级
                    string 等级 = tb.Rows[i]["产出等级"].ToString();
                    if (等级.ToUpper().Contains("A") || 等级.ToUpper().Contains("B"))
                    {
                        decimal.TryParse(tb.Rows[i]["产出重量"].ToString(), out decimal a);
                        总产出重量 += a;
                    }
                    //decimal.TryParse(tb.Rows[i]["产出重量"].ToString(),out decimal a);
                    decimal.TryParse(tb.Rows[i]["长度"].ToString(), out decimal b);
                    //总产出重量 += a;
                    产出米数 += b;
                    decimal.TryParse(tb.Rows[i]["面积"].ToString(), out decimal 面积);
                    总面积 += 面积;
                }
            }

            DataTable tb2 = pub.GetDataTable(pub.ConnectionString, sql2);
            if (tb2 != null && tb2.Rows.Count > 0)
            {
                for (int i = 0; i < tb2.Rows.Count; i++)
                {
                    decimal.TryParse(tb2.Rows[i]["投入重量"].ToString(), out decimal a);
                    总投入重量 += a;
                }
            }
            this.uiTextBox投入重量合计.Text = 总投入重量.ToString();
            this.uiTextBox产出重量合计.Text = 总产出重量.ToString();
            this.uiTextBox产出米数合计.Text = 产出米数.ToString();
            uiTextBox面积.Text = 总面积.ToString();

            if (总投入重量 > 0)
            {
                this.uiTextBox产出率.Text = Math.Round((pub.Todecimal(总产出重量 / 总投入重量) * 100), 2).ToString();
            }
            else
            {
                this.uiTextBox产出率.Text = "";
            }

            this.uiTextBox卷数.Text = tb.Rows.Count.ToString();
            return tb;
        }

        private void Form镀锌投入产出表_Load(object sender, EventArgs e)
        {

            this.uiDatePicker开始日期.Value = DateTime.Today.AddDays(-1);
            this.uiDatePicker结束日期.Value = DateTime.Today.AddDays(-1);
            pub.ListRankAll(pub.全局工厂编号, this.uiComboBox查询等级);
            pub.ListWorkLineAll(pub.全局工厂编号, FormWorkType, this.uiComboBox查询产线);
            pub.ListWorkTeamAll(pub.全局工厂编号, "", this.uiComboBox查询班组);
            string 当前班组 = pub.Get员工所属班组(pub.sys_user.UserTrueName);
        }

        private void uiButton导出_Click(object sender, EventArgs e)
        {
            string path;
            //DataTable tb = ListOutPutData();
            string guid = Guid.NewGuid().ToString();
            DataTable tb = ListOutPutData(guid);
            saveFileDialog1.Filter = "Excel文件 (*.XLSX)|*.XLSX";
            if (this.saveFileDialog1.ShowDialog(this) == DialogResult.OK)
            {
                path = this.saveFileDialog1.FileName;
                if (!File.Exists(path))
                {
                    MiniExcel.SaveAs(path, tb, true, "sheet1", ExcelType.XLSX);
                    deleteTable(guid);
                }
                else
                {
                    MessageBox.Show("文件已经存在,请重试!", "提示", MessageBoxButtons.OK);
                    return;
                }

            }
        }
        private void deleteTable(string guid)
        {
            string sql = "delete from Bus_镀锌投入产出 where guid = '" + guid + "';delete from Temp_投入产出表 where guid = '" + guid + "';";
            pub.ExecuteSql(pub.ConnectionString, sql);
        }


        private void uiDataGridView1_CellMouseDown(object sender, DataGridViewCellMouseEventArgs e)
        {
            开始行 = e.RowIndex;
            开始列 = e.ColumnIndex;
        }

        private void uiDataGridView1_CellMouseUp(object sender, DataGridViewCellMouseEventArgs e)
        {
            decimal 总投入重量 = 0;
            decimal 投入重量 = 0;
            decimal 总产出重量 = 0;
            decimal 总产面积 = 0;
            结束列 = e.ColumnIndex;

            string guid = Guid.NewGuid().ToString();
            int 总卷数 = 0;
            结束行 = e.RowIndex;

            if (开始行 == 0 && 结束行 == 0)
            {

                this.uiTextBox卷数.Text = "1";
                this.uiTextBox投入重量合计.Text = this.uiDataGridView1.Rows[开始行].Cells["E投入重量"].Value.ToString();
                this.uiTextBox产出重量合计.Text = this.uiDataGridView1.Rows[开始行].Cells["E产出重量"].Value.ToString();
                this.uiTextBox面积.Text = this.uiDataGridView1.Rows[开始行].Cells["E面积"].Value.ToString();
            }

            if (开始行 >= 0 && 结束行 > 0)
            {
                if (开始行 > 结束行)
                {
                    int k = 开始行;
                    开始行 = 结束行;
                    结束行 = k;
                }
                string sql = "delete Temp_投入汇总表 where guid='" + guid + "'";
                pub.ExecuteSql(pub.ConnectionString, sql, "delete Temp_投入汇总表 ");
                for (int j = 0; j < uiDataGridView1.Rows.Count; j++)
                {
                    //取消之前的选择
                    this.uiDataGridView1.Rows[j].Cells["E选择"].Value = false;
                }
                for (int i = 开始行; i <= 结束行; i++)
                {


                    投入重量 = pub.Todecimal(this.uiDataGridView1.Rows[i].Cells["E投入重量"].Value.ToString());
                    string 投入卷号 = this.uiDataGridView1.Rows[i].Cells["E投入卷号"].Value.ToString();
                    sql = " insert into Temp_投入汇总表 (guid, 卷号, 投入重量) ";
                    sql += " values('" + guid + "','" + 投入卷号 + "','" + 投入重量 + "')";
                    pub.ExecuteSql(pub.ConnectionString, sql, "insert into Temp_投入汇总表 ");

                    总产出重量 += pub.Todecimal(this.uiDataGridView1.Rows[i].Cells["E产出重量"].Value.ToString());
                    总产面积 += pub.Todecimal(this.uiDataGridView1.Rows[i].Cells["E面积"].Value.ToString());
                    总卷数++;
                }
                sql = "select  isnull(  sum(t.投入重量),0)  from  (select distinct 卷号, 投入重量 from Temp_投入汇总表 where guid='" + guid + "' ) t ";
                DataTable tb = pub.GetDataTable(pub.ConnectionString, sql);
                if (tb.Rows.Count > 0)
                {
                    总投入重量 = pub.Todecimal(tb.Rows[0][0].ToString());
                }
                else
                {
                    总投入重量 = 0;
                }
                sql = "delete Temp_投入汇总表 where guid='" + guid + "'";
                pub.ExecuteSql(pub.ConnectionString, sql, "delete Temp_投入汇总表 ");

                if (总投入重量 != 0)
                {
                    this.uiTextBox产出率.Text = Math.Round((pub.Todecimal(总产出重量 / 总投入重量) * 100), 2).ToString();
                }
                else
                {
                    this.uiTextBox产出率.Text = "";
                }
                this.uiTextBox卷数.Text = 总卷数.ToString();
                this.uiTextBox投入重量合计.Text = Math.Round(总投入重量, 3).ToString();
                this.uiTextBox产出重量合计.Text = Math.Round(总产出重量, 3).ToString();
                this.uiTextBox面积.Text = Math.Round(总产面积, 3).ToString();
            }
        }

        private void uiButton修改_Click(object sender, EventArgs e)
        {
            //判断是不是同一列
            if (开始列 != 结束列)
            {
                MessageBox.Show("请延续同一列拖动选择，否则不知道要改哪一列。");
                return;
            }
            else
            {
                string columnName = uiDataGridView1.Columns[开始列].DataPropertyName;
                List<string> canUpdateColumns = new List<string>() { "产出规格", "产出材质", "包装", "客户", "去向", "锌层" };
                if (canUpdateColumns.Contains(columnName))
                {
                    if (MessageBox.Show("确定要修改【" + columnName + "】的值么？", "提示", MessageBoxButtons.OKCancel) == DialogResult.OK)
                    {
                        //能修改的列


                        //要修改的卷号
                        for (int i = 0; i < uiDataGridView1.Rows.Count; i++)
                        {
                            if (uiDataGridView1.Rows[i].Cells["E选择"].Value != null && (bool)uiDataGridView1.Rows[i].Cells["E选择"].Value)
                            {
                                string volumCode = uiDataGridView1.Rows[i].Cells["E产出卷号"].Value.ToString();
                                //卷号有没有打印入库过
                                if (querIsLabelPrinted(volumCode))
                                {
                                    MessageBox.Show("["+ volumCode + "]已经打印，无法修改。");
                                    return;
                                }
                                //选择的行批量修改
                                int r = edit(columnName, uiTextBox修改值.Text.Trim(), volumCode);
                                if (r < 0)
                                {
                                    MessageBox.Show("【" + volumCode + "】的【" + columnName + "】更新失败");
                                }
                                else
                                {
                                    uiDataGridView1.Rows[i].Cells["E"+columnName].Value = uiTextBox修改值.Text.Trim();
                                }
                            }
                        }


                    }
                }
                else
                {
                    MessageBox.Show(columnName+"列不可修改");
                    return;
                }

            }
        }

        private bool querIsLabelPrinted(string volumCode)
        {
            string sql = "select 生产批号 from Bus_LablePrint where 生产批号 = '"+ volumCode + "' and 是否已打印 = '是'";
            DataTable dt = pub.GetDataTable(pub.ConnectionString, sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        private int edit(string 列,string 值,string 卷号)
        {
            string ZincOutput列 = "";
            string LabelPrint列 = "";
            switch (列)
            {
                case "产出规格":
                    ZincOutput列 = "OutputSize";
                    LabelPrint列 = "规格";
                    break;
                case "产出材质":
                    ZincOutput列 = "OutputMaterial";
                    LabelPrint列 = "钢号";
                    break;
                case "包装":
                    ZincOutput列 = "Package";
                    LabelPrint列 = "包装形式";
                    break;
                case "客户":
                    ZincOutput列 = "Customer";
                    LabelPrint列 = " 客户";
                    break;
                case "去向":
                    ZincOutput列 = "OutputNext";
                    break;
                case "锌层":
                    ZincOutput列 = "ZincThick";
                    LabelPrint列 = "锌层";
                    break;
                default:
                    break;
            }


            string sqlupdate = @"update Bus_ZincOutput set "+ ZincOutput列 + "= '"+值+ "' where OutputVolumeNew = '"+卷号+"';";
            sqlupdate += @"update Bus_LablePrint set "+ LabelPrint列 + " = '"+ 值 + "' where 生产批号 = '"+ 卷号 + "';";
            int r = pub.ExecuteSql(pub.ConnectionString, sqlupdate, "镀锌投入产出表更新字段值");
            return r;
        }

        private void uiComboBox查询产线_SelectedIndexChanged(object sender, EventArgs e)
        {
            pub.ListWorkTeamAll2(pub.全局工厂编号, this.uiComboBox查询产线.SelectedValue.ToString(), this.uiComboBox查询班组);
        }
    }
}
